using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using emp.Model;
using emp.IDAL;
using emp.Utility;
using System.Collections;
namespace emp.SqlServerDAL
{
    public class DuplicateRule :IDuplicateRule
    {
        public DataTable selectDataDupList()
        {
            string SQL_SELECT_DATADUPLIST = "select a.DDR_PRIORITY,b.QCA_ALIAS_NAME name1,c.QCA_ALIAS_NAME name2,d.QCA_ALIAS_NAME name3,"
                + "e.QCA_ALIAS_NAME name4,ISNULL(DDR_STATUS,'N') DDR_STATUS from DATA_DUPLICATE_RULE a left join "
                +"QUEST_COLUMN_ALIAS b on a.DDR_SOURCE_FIELD1 = b.QCA_FIELD_NAME left join QUEST_COLUMN_ALIAS c "
                +"on a.DDR_SOURCE_FIELD2 = c.QCA_FIELD_NAME left join QUEST_COLUMN_ALIAS d "
                +"on a.DDR_SOURCE_FIELD3 = d.QCA_FIELD_NAME left join QUEST_COLUMN_ALIAS e on "
                + "a.DDR_SOURCE_FIELD4 = e.QCA_FIELD_NAME order by DDR_PRIORITY ";

            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_DTC_INV))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL_SELECT_DATADUPLIST, conn);
                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            return dt;
        }

        public DataTable getInvidualColumn()
        {
            string sqlstr = "select QCA_FIELD_NAME,QCA_ALIAS_NAME from QUEST_COLUMN_ALIAS where QCA_FIELD_NAME "
                + "not in ('IND_URN','IND_CUSTOMER_CODE','IND_NFC','IND_FIELDS_1','IND_FIELDS_2','IND_FIELDS_3',"
                + "'IND_FIELDS_4','IND_FIELDS_5') and QCA_TARGET_TABLE='INDIVIDUALS' and QCA_FIELD_STATUS='Y'";
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_DTC_INV))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sqlstr, conn);
                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            return dt;
        }

        public void addrule(string[] strvalue)
        {
            string sqlstr = "insert into DATA_DUPLICATE_RULE(DDR_TYPE,DDR_PRIORITY,DDR_SOURCE_FIELD1,DDR_SOURCE_FIELD2,DDR_SOURCE_FIELD3,DDR_SOURCE_FIELD4,DDR_STATUS)"
                + " values('" + strvalue[0] + "','" + strvalue[1] + "','" + strvalue[2] + "','" + strvalue[3] + "','" + strvalue[4] + "',"
                +"'" + strvalue[5] + "','" + strvalue[6] + "')";

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sqlstr, null);
                        trans.Commit();

                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

        public void updaterule(string[] strvalue)
        {
            string sqlstr = "update DATA_DUPLICATE_RULE set DDR_SOURCE_FIELD1='" + strvalue[2] + "',"
                + "DDR_SOURCE_FIELD2='" + strvalue[3] + "',DDR_SOURCE_FIELD3='" + strvalue[4] + "',"
                + "DDR_SOURCE_FIELD4='" + strvalue[5] + "',DDR_STATUS='" + strvalue[6] + "' "
                + "where DDR_PRIORITY='" + strvalue[1] + "'";

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sqlstr, null);
                        trans.Commit();

                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            } 
        }

        public bool existsrule(string[] strvalue)
        {
            bool ReturnValue = false;
            string sqlstr = "select 1 from DATA_DUPLICATE_RULE where ISNULL(DDR_SOURCE_FIELD1,'')='" + strvalue[2] + "' AND "
            + "ISNULL(DDR_SOURCE_FIELD2,'')='" + strvalue[3] + "' AND "
            + "ISNULL(DDR_SOURCE_FIELD3,'')='" + strvalue[4] + "' AND "
            + "ISNULL(DDR_SOURCE_FIELD4,'')='" + strvalue[5] + "'";

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sqlstr, null))
            {
                while (rdr.Read())
                {
                    ReturnValue = true;
                }
            }
            return ReturnValue;
        }

        public DataTable getRuleInfo(string priority)
        {
            string sqlstr = "select DDR_SOURCE_FIELD1,DDR_SOURCE_FIELD2,DDR_SOURCE_FIELD3,"
                + "DDR_SOURCE_FIELD4,DDR_STATUS from DATA_DUPLICATE_RULE where DDR_PRIORITY="+priority;

            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_DTC_INV))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sqlstr, conn);
                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            return dt;
        }
    }
}
